import re
import spacy
from pymysql import connect as mysqlClient

from user_pwd import *

nlp = spacy.load('en_core_web_sm')


def get_video_by_mysql():
	client = mysqlClient('172.19.167.64', 'root', mysql_pwd, 'world', 3306)
	cur = client.cursor()
	cur.execute(
		'select video_id,split,video_name,difficulty,is_green,visual_vid,video_img,check_time from tt_video where isPublish=1')
	cur.close()
	client.close()
	return cur.fetchall()





def writevrt(video, f, metaf):
	# 0:video_id,1:split,2:video_name,3:difficulty,4:is_green,5:visual_vid,6:video_img,7:check_time
	print(f'<text id="{video[0]}">', file=f)
	s = video[1]
	processedText = re.sub(r"\s+", " ", s.strip())
	processedText = re.sub(r"<|>|\(|\)|\t|", "", processedText)

	doc = nlp(processedText)
	for sentence in doc.sents:
		print('<s>', file=f)
		for token in sentence:
			word = token.text
			lemma = token.lemma_
			if lemma == "-PRON-":
				lemma = token.lower_
			print("\t".join([word, token.tag_, lemma, token.pos_]), file=f)
		print('</s>', file=f)
	print('</text>', file=f)
	writeMetaData(video, processedText, metaf)


def writeMetaData(video, text, metaf):
	#  0:video_id,1:split,2:video_name,3:difficulty,4:is_green,5:visual_vid,6:video_img,7:check_time
	print(video[0], video[2], int(video[3]), video[4], video[5], video[6], video[7], text, sep='\t', file=metaf)


def create_file(vrt_file, meta_file):
	f = open(vrt_file, 'w', encoding='utf-8')
	metaf = open(meta_file, 'w', encoding='utf-8')
	for video in get_video_by_mysql():
		writevrt(video, f, metaf)
